package gentle.service.impl;

import gentle.base.SqlMapper;
import gentle.base.StringUtil;
import gentle.entity.CheckoutMethodInfo;
import gentle.entity.SearchPaymentTotalInfo;
import gentle.service.IPaymentTotalService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class PaymentTotalServiceImpl implements IPaymentTotalService {
    @Autowired
    private SqlMapper sqlMapper;

    public List<Map<String, Object>> getList(SearchPaymentTotalInfo searchInfo, String reach){
        String tableHead = searchInfo.getAbbreviation();
        String selectsql = "select t.id,t.checkout_method from  " + tableHead + "recharge_info"  + " t left join checkout_method_info t1 on t.checkout_method_id=t1.id where 1=1";  //0:正常方式，1:积分，2:优惠券 , 3:储值
        if (searchInfo.getStartTime() != null && searchInfo.getStartTime() != "") {
            selectsql += " and t.recharge_time >= '" + searchInfo.getStartTime() + "' ";
        }
        if (searchInfo.getEndTime() != null && searchInfo.getEndTime() != "") {
            selectsql += " and t.recharge_time <= '" + searchInfo.getEndTime() + "' ";
        }
        selectsql += " group by t.checkout_method order by t1.num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(selectsql, CheckoutMethodInfo.class);
        String sql = "select * from(select a.customer_id as customerId, b.customer_name as '客户姓名',b.customer_phone as '电话'," +
                " b.customer_sex as '性别', b.customer_age as '年龄',a.recharge_time as '时间'," +
                " a.recharge_people as '收费人', b.customer_status as '客户状态', ifnull(a.tc_name,c.tc_name) as 'tc', ";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " a.remarks as  '备注' from " + tableHead + "recharge_info" + " a left join " + tableHead + "customer_info" + " b on a.customer_id=b.id" +
                " left join " + tableHead + "deal_info" + " c on a.deal_id=c.id " +
                " where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchInfo.getFieldTc() + "' or a.tc_name ='"+searchInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchInfo.getEndTime() + "'";
        }
        sql += " group by a.id order by a.recharge_time desc) t union all  " +
                " select '' as customerId, '合计' as '客户姓名','' as '客户电话','' as '性别','' as '年龄','' as  '时间'," +
                " '' as '收费人',sum(t.actualAccountBalance) AS '客户状态','' as 'tc',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " sum(t." + list1.get(i).getCheckoutMethod() + ") ,";
            }
        }
        sql += " '' as  '备注' from (select '' as customerId, '合计' as '客户姓名','' as '客户电话','' as '性别','' as '年龄','' as  '时间',  " +
                " '' as '收费人'," +
                " a.actual_account_balance as actualAccountBalance," +
                "'' as 'tc',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " '' as  '备注' from " + tableHead + "recharge_info" + " a left join " + tableHead + "customer_info" + " b on a.customer_id=b.id  " +
                " left join " + tableHead + "deal_info" + " c on a.deal_id=c.id where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchInfo.getFieldTc() + "' or a.tc_name ='"+searchInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchInfo.getDoctor())) {
            sql += " and b.doctor = '" + searchInfo.getDoctor() + "'";
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchInfo.getEndTime() + "'";
        }
        sql += " group by a.id ) t";
        List<Map<String, Object>> list = sqlMapper.selectList(sql);
        return list;
    }

    public HashMap sumBorrowAmount(String reach,String checkoutMethod,String fieldTc,String startTime,String customerStatus,String endTime,String abbreviation){
        String sql = " (select sum(t.borrowAmount_sum) as borrowAmount_sum from (select t1.actual_account_balance as borrowAmount_sum " +
                " from " + abbreviation + "recharge_info" + " t1 left join " + abbreviation + "customer_info" + " t2 on t1.customer_id=t2.id " +
                " left join " + abbreviation + "deal_info t3 on t1.deal_id=t3.id where 1=1" +
                " and t1.isEarnes!=3 and !(t1.isEarnes=2 and t1.section_performance=0 and t1.hospital_performance=0) ";
        if (StringUtil.isNotEmpty(reach)) {
            sql += "  and (t2.customer_name like CONCAT('%','" + reach + "','%') or t2.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " t2.wechat_number like CONCAT('%','" + reach + "','%') or t2.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or t2.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(checkoutMethod)) {
            sql += " and t1.checkout_method = '" + checkoutMethod + "'";
        }
        if (StringUtil.isNotEmpty(fieldTc)) {
            sql += " and (t3.tc_name = '" + fieldTc + "' or t1.tc_name='" + fieldTc + "') ";
        }
        if (StringUtil.isNotEmpty(customerStatus)) {
            sql += " and t2.customer_status = '" + customerStatus + "'";
        }
        if (StringUtil.isNotEmpty(startTime)) {
            sql += " and t1.recharge_time >= '" + startTime + "'";
        }
        if (StringUtil.isNotEmpty(endTime)) {
            sql += " and t1.recharge_time <= '" + endTime + "'";
        }
        sql += " group by t1.id) t) ";
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("borrowAmount_sum", result.get("borrowAmount_sum"));
        } else {
            map.put("borrowAmount_sum", "0");
        }
        return map;
    }

    public void paymentTableExport(SearchPaymentTotalInfo info, HttpServletResponse response) {
        try {
            List<LinkedHashMap<String, Object>> list = this.paymentTableExport(info,info.getReach());
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 27; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell;
            for (int i = 0;  i< list.size(); i++) {
                Map<String, Object> map = list.get(i);
                int a=0;
                for (String key : map.keySet()) {
                    cell = row.createCell(a);
                    cell.setCellValue(key);
                    cell.setCellStyle(style);
                    a=a+1;
                }
            }
            // 填充数据
            for (int i = 0;  i< list.size(); i++) {
                Map<String, Object> map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= map.size(); j++) {
                    row.createCell(j).setCellStyle(style);
                }
                int a=0;
                for (String key : map.keySet()) {
                    if(info.getPhonePrivilegesexp()!=1 && "电话".equals(key)) {
                        row.getCell(a).setCellValue("无");
                    }else{
                        row.getCell(a).setCellValue(map.get(key).toString());
                    }
                    a=a+1;
                }
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<LinkedHashMap<String, Object>> paymentTableExport(SearchPaymentTotalInfo searchPaymentTotalInfo, String reach) {
        String tableHead = searchPaymentTotalInfo.getAbbreviation();
        String selectsql = "select t.id,t.checkout_method from  " + tableHead +"recharge_info t left join checkout_method_info t1 on t.checkout_method_id=t1.id where 1=1";  //0:正常方式，1:积分，2:优惠券 , 3:储值
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            selectsql += " and t.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            selectsql += " and t.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        selectsql += " group by t.checkout_method order by t1.num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(selectsql, CheckoutMethodInfo.class);
        String sql = "select * from(select b.customer_name as '客户姓名',b.customer_phone as '电话'," +
                "( CASE b.customer_sex " +
                " WHEN 1 THEN " +
                " '男' " +
                " WHEN 2 THEN " +
                " '女' " +
                " ELSE " +
                " '未知' " +
                " END " +
                " ) as '性别', b.customer_age as '年龄',a.recharge_time as '时间'," +
                " ifnull(ifnull(a.tc_name,c.tc_name),'') as '现场TC', a.recharge_people as '收费人', b.customer_status as '客户状态', ";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " a.remarks as  '备注' from " + tableHead +"recharge_info a left join " + tableHead +"customer_info b on a.customer_id=b.id" +
                " left join " + tableHead +"deal_info c on a.deal_id=c.id " +
                " where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchPaymentTotalInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' or a.tc_name ='"+searchPaymentTotalInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " group by a.id order by a.recharge_time desc) t union all  " +
                " select  '合计' as '客户姓名','' as '电话','' as '性别','' as '年龄','' as  '时间'," +
                " '' as '现场TC', '' as '收费人',sum(t.actualAccountBalance) AS '客户状态',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " sum(t." + list1.get(i).getCheckoutMethod() + ") ,";
            }
        }
        sql += " '' as  '备注' from (select '合计' as '客户姓名','' as '电话','' as '性别','' as '年龄','' as  '时间',  " +
                " '' as '现场TC','' as '收费人'," +
                " a.actual_account_balance as actualAccountBalance, ";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " '' as  '备注' from " + tableHead+"recharge_info a left join " + tableHead+"customer_info b on a.customer_id=b.id  " +
                " left join " + tableHead+"deal_info c on a.deal_id=c.id where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchPaymentTotalInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' or a.tc_name ='"+searchPaymentTotalInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getDoctor())) {
            sql += " and b.doctor = '" + searchPaymentTotalInfo.getDoctor() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " group by a.id ) t";
        List<LinkedHashMap<String, Object>> list = sqlMapper.selectListLink(sql);
        return list;
    }

}